package com.workspace.js_website.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 日志表相关数据库操作
 */

@Mapper
@Repository("logMapper")
public interface LogMapper {
    @Insert("<script>" +
            "insert into log(USER_ID, BEHAVIOR, OCCURRENCE_TIME, IP) " +
            "values(" +
            "<if test='userId!=\"\"'>#{userId}, </if>" +
            "<if test='userId==\"\"'>null, </if>" +
            "#{behavior}, sysdate, #{ip})" +
            "</script>")
    void addLog(String userId, String behavior, String ip);

    @Select("select count(*) from log where ip = #{ip} and occurrence_time > sysdate - ${time}")
    int checkIp(String behavior, String ip, String time);

    @Select("select count(*) from log " +
            "where behavior like '%${behavior}%' " +
            "and occurrence_time > to_date(#{start}, 'yyyy-MM-dd hh24:mi:ss') " +
            "and occurrence_time < to_date(#{end}, 'yyyy-MM-dd hh24:mi:ss')")
    int getLogCount(String behavior, String start, String end);

    @Select("select count(*) from users " +
            "where registration_time > to_date(#{start}, 'yyyy-MM-dd hh24:mi:ss') " +
            "and registration_time < to_date(#{end}, 'yyyy-MM-dd hh24:mi:ss')")
    int getRegistrationCount(String start, String end);

    @Select("select u.name, l.behavior, to_char(l.occurrence_time, 'yyyy-MM-dd hh24:mi:ss') time from log l,users u " +
            "where u.user_id = l.user_id " +
            "and u.name like '%${name}%' " +
            "and l.behavior like '%requestProduct%'")
    List<Map<String, String>>  getProductsByName(String name);

    @Select("select l.behavior, to_char(l.occurrence_time, 'yyyy-MM-dd hh24:mi:ss') time, u.name, u.email, u.mobile, u.company " +
            "from log l,users u " +
            "where u.user_id = l.user_id " +
            "and l.behavior like '%${product}%'")
    List<Map<String, String>>  getUsersByProduct(String product);
}
